<?php
/**
 * SqlAction.php
 * phpstorm_company_name_str - phpstorm_company_product_name
 * ***************************************************************************************************************************
 * 版权声明: Copyright ©2019 phpstorm_company_name_str All rights reserved
 * 官方网站: phpstorm_company_product_url
 * 许可声明: 这不是一个自由软件！您只能在购买源代码后或者经 phpstorm_company_name_str 官方授权的前提下对程序代码进行修改和使用，
 * 任何企业或个人都不得在源代码上以任何形式任何目的的再次发布和销售以及镜像或复制,包括但不限于已购买源代码和官方授权者。
 * 警 告  : 如若您或贵司未经正式购买渠道获得授权，请立即停止侵权行为，我们已经委派 phpstorm_company_auth_legal 作为我们侵权
 * 起诉的法律代表，还请您或贵司尊重商业版权以及劳动成果，自觉停止侵权行为。您也可匿名举报侵权行为，一旦您的举报被证实，我们
 * 将会以本版本零售价的 phpstorm_company_product_percent_money% 现金给您，以便作为您的辛苦奖励。同时，我们也将竭尽全力保证您的隐私安全。举报邮箱: phpstorm_company_product_report_mail
 * 协议约定: 本产品遵守 << phpstorm_company_product_protocol >> 的约束,您或贵司也可以访问 phpstorm_company_product_protocol 了解更多详情信息。
 * 致 谢  : 感谢您或贵司对我们产品的支持和热爱，我们将不定期对本产品进行更新和修复bug。还请您和贵司多多指导。更多问题资讯请访问
 * phpstorm_company_product_help_url ,如果您正在对此版本进行二次开发，提bug或者需求可以发送到@author邮箱,或者提交工单。
 * 工单地址是: phpstorm_company_product_ticket_url 我们将尽快为您提出合理的解决方案。
 * 其 他  : 您或贵司也可以登录 phpstorm_company_product_doc_url 查看相关的文档资料以及FAQ！
 * ***************************************************************************************************************************
 * @product ctcms
 * @desc
 * @author justary <justary@126.com>
 * @date 2024/05/27 14:48
 * @version phpstorm_company_product_version
 * @copyright phpstorm_company_product_copyright
 */

/**
 * @package app\data\components
 *
 * @filesource SqlAction.php
 * @author justary <justary@126.com>
 * @date 2024/05/27 14:48
 * @link phpstorm_product_licence
 * @version phpstorm_company_product_version
 * @copyright phpstorm_company_product_copyright
 */


namespace app\data\components;


use think\App;
use think\facade\Env;

class SqlAction
{
  protected  $connection;
  protected  $dbConfig;
  protected  $rootPath;

  public function __construct(){
    //$this->rootPath = App::getInstance()->getAppPath().'console'.DIRECTORY_SEPARATOR.'db'.DIRECTORY_SEPARATOR;
    $this->rootPath = App::getInstance()->getRootPath().'public'.DIRECTORY_SEPARATOR.'db'.DIRECTORY_SEPARATOR;
    $this->dbConfig = [
      'type'            => Env::get('database.DB_TYPE', 'mysql'),
      // 服务器地址
      'hostname'        => Env::get('database.DB_HOST', '127.0.0.1'),
      // 数据库名
      'database'        => Env::get('database.DB_NAME', ''),
      // 用户名
      'username'        => Env::get('database.DB_USER', 'root'),
      // 密码
      'password'        => Env::get('database.DB_PASS', ''),
      // 端口
      'hostport'        => Env::get('database.DB_PORT', '3306'),
      // 数据库连接参数
      'params'          => [],
      // 数据库编码默认采用utf8
      'charset'         => Env::get('database.DB_CHARSET', 'utf8'),
    ];
    $dsn = "{$this->dbConfig['type']}:host={$this->dbConfig['hostname']};port={$this->dbConfig['hostport']};dbname={$this->dbConfig['database']};charset={$this->dbConfig['charset']}";
    $username = "{$this->dbConfig['username']}";
    $password = "{$this->dbConfig['password']}";
    try {
      $this->connection = new \PDO($dsn,$username,$password);
    } catch (\PDOException $e){
      echo $e->getMessage();
    }

  }

  public function run() {

    $summaryContent = '';
    $mdContent = '';
    $dbPath = $this->rootPath.'1-db/';
    if(!file_exists($dbPath)){
      mkdir($dbPath,0777,true);
    }
    $summary = $this->rootPath.'1-db/README.md';
    if(file_exists($summary)){
      unlink($summary);
    }
    touch($summary);
    echo '开始执行...'.PHP_EOL;

    $sql2 = "select concat(round(sum((data_length + index_length)/1024/1024),2),'MB') as data from information_schema.tables where table_schema='".$this->dbConfig['database']."'";
    $sth1 = $this->connection->prepare($sql2);
    $sth1->execute();
    $rst2 = $sth1->fetch($this->connection::FETCH_ASSOC);
    $sql3 = "select COUNT(*) as tableCount from information_schema.tables where table_schema='".$this->dbConfig['database']."' GROUP BY table_schema";
    $sth3 = $this->connection->prepare($sql3);
    $sth3->execute();
    $rst4 = $sth3->fetch($this->connection::FETCH_ASSOC);

    $summaryContent = $summaryContent . '# 数据库'.PHP_EOL.PHP_EOL;
    $summaryContent = $summaryContent . '## 数据库信息'.PHP_EOL.PHP_EOL;
    $summaryContent = $summaryContent . '- 数据库大小：'.$rst2['data'].PHP_EOL.PHP_EOL;
    $summaryContent = $summaryContent . '- 数据表数量：'.$rst4['tableCount'].PHP_EOL.PHP_EOL;
    $tableData = [];
    foreach ($this->connection->query("select * from information_schema.tables where table_schema='{$this->dbConfig['database']}'",$this->connection::FETCH_ASSOC) as $row) {
      echo '写入'.$row['TABLE_NAME'].'表信息'.PHP_EOL;
      $dirname = $this->rootPath.'2-table';

      $fileName = $dirname.DIRECTORY_SEPARATOR.$row['TABLE_NAME'].'.md';
      if(!is_dir($dirname)){
        mkdir($dirname,0777,true);
      }

      if(file_exists($fileName)){
        unlink($fileName);
        touch($fileName);
      }else{
        touch($fileName);
      }
      $sql1 = "select concat(round(sum((data_length + index_length)/1024/1024),2),'MB') as data from information_schema.tables where table_schema = '".$this->dbConfig['database']."' and table_name = '".$row['TABLE_NAME']."'";
      $sth = $this->connection->prepare($sql1);
      $sth->execute();
      $rst = $sth->fetch($this->connection::FETCH_ASSOC);
      $tableMemSql = "select concat(round(sum(data_length/1024/1024),2),'MB') as data from information_schema.tables where table_schema = '".$this->dbConfig['database']."' and table_name = '".$row['TABLE_NAME']."'";
      $tableMemSth = $this->connection->prepare($tableMemSql);
      $tableMemSth->execute();
      $tableMemRes = $tableMemSth->fetch($this->connection::FETCH_ASSOC);
      $indexMemSql = "select concat(round(sum(index_length/1024/1024),2),'MB') as data from information_schema.tables where table_schema = '".$this->dbConfig['database']."' and table_name = '".$row['TABLE_NAME']."'";
      $indexMemSth = $this->connection->prepare($indexMemSql);
      $indexMemSth->execute();
      $indexMemRes = $indexMemSth->fetch($this->connection::FETCH_ASSOC);
      $tableName = '~';
      $tableComment = '~';
      if(!empty($row['TABLE_COMMENT'])){

        $tableComment = '';
        $tableName = substr($row['TABLE_COMMENT'],0,strrpos($row['TABLE_COMMENT'],'||'));
        $tableComment .= PHP_EOL;
        //新增字段
        if(preg_match_all('%\@\w+%u', $row['TABLE_COMMENT'], $matches)) {
          foreach ($matches as $key => $value){
            foreach ($value as $item => $vt){
              $tableComment .= '- '.str_replace('@','新增字段：',$vt).PHP_EOL;
            }
          }
        }

        // 废弃字段
        if(preg_match_all('%\#\w+.\w+[-.]\w%', $row['TABLE_COMMENT'], $matches)) {
          foreach ($matches as $key => $value){
            foreach ($value as $item => $vt){
              $editField = explode("__",$vt);
              $tableComment .= '- '.str_replace('#','废弃字段：',$editField[0]).' 版本号：'.$editField[1].PHP_EOL;
            }
          }
        }

        // 修改字段
        if(preg_match_all('%\$\w+%u', $row['TABLE_COMMENT'], $matches)) {
          foreach ($matches as $key => $value){
            foreach ($value as $item => $vt){
              $tableComment .= '- '.str_replace('$','修改字段：',$vt).PHP_EOL;
            }
          }
        }

        // 删除索引
        if(preg_match_all('%\&\w+%u', $row['TABLE_COMMENT'], $matches)) {
          foreach ($matches as $key => $value){
            foreach ($value as $item => $vt){
              $tableComment .= '- '.str_replace('&','删除索引：',$vt).PHP_EOL;
            }
          }
        }

        // 新增索引
        if(preg_match_all('%\^\w+%u', $row['TABLE_COMMENT'], $matches)) {
          foreach ($matches as $key => $value){
            foreach ($value as $item => $vt){
              $tableComment .= '- '.str_replace('^','新增索引：',$vt).PHP_EOL;
            }
          }
        }
      }
      array_push($tableData,['table'=>$row['TABLE_NAME'],'name'=>$tableName,'capacity'=>$rst['data']]);
      $created_at = $row['CREATE_TIME'];
      $updated_at = empty($row['UPDATE_TIME']) ? $row['CREATE_TIME'] : $row['UPDATE_TIME'];
      $mdContent = $mdContent . '# '.str_replace('_','-',$row['TABLE_NAME']).'【'.$rst['data'].'】'.PHP_EOL.PHP_EOL.PHP_EOL;
      $mdContent = $mdContent . '## 表信息'.PHP_EOL.PHP_EOL.PHP_EOL.PHP_EOL;
      $mdContent = $mdContent . '- 表名：【'.$row['TABLE_NAME'].'('.$tableName.')】'.PHP_EOL.PHP_EOL;
      $mdContent = $mdContent . '- 引擎：【'.$row['ENGINE'].'】'.PHP_EOL.PHP_EOL;
      $mdContent = $mdContent . '- 默认排序规则：【'.$row['TABLE_COLLATION'].'】'.PHP_EOL.PHP_EOL;
      $mdContent = $mdContent . '- 表创建时间：【'.$created_at.'】'.PHP_EOL.PHP_EOL;
      $mdContent = $mdContent . '- 表更新时间：【'.$updated_at.'】'.PHP_EOL.PHP_EOL;
      $mdContent = $mdContent . '- 表空间大小：【'.$tableMemRes['data'].'】'.PHP_EOL.PHP_EOL;
      $mdContent = $mdContent . '- 表索引大小：【'.$indexMemRes['data'].'】'.PHP_EOL.PHP_EOL;
      $mdContent = $mdContent . '## 更新日志'.PHP_EOL.PHP_EOL.PHP_EOL.PHP_EOL . $tableComment;
      //$sql = "select * from information_schema.columns where "."table_schema=".$this->dbConfig['database'];
      $mdContent = $mdContent . PHP_EOL.PHP_EOL.'---'.PHP_EOL.PHP_EOL;
      $mdContent = $mdContent . '## 表结构'.PHP_EOL.PHP_EOL.PHP_EOL.PHP_EOL;
      $mdContent = $mdContent . '| 序号 | 字段名 | 字段类型(长度) | 小数点 | 是否空值 | 是否自增长 | 默认 | 主键 | 注释 |'.PHP_EOL;
      $mdContent = $mdContent . '| :---: | :---: | :---: | :---: | :---: | :---: | :---: | :---: | :--- |'.PHP_EOL;
      $sql = "SELECT * FROM information_schema.COLUMNS WHERE table_name ='".$row['TABLE_NAME']."' AND table_schema = '".$this->dbConfig['database']."'";
      $list = $this->connection->query($sql,$this->connection::FETCH_ASSOC);
      $index_var = '';
      if(empty($list)){
        echo 'empty content';
      }else{

        //结束索引

        $ddl = '';
        $is_pri = "  PRIMARY KEY (";
        $columnLength = 0;
        $columnArray = [];
        $k = 1;
        foreach ($list as $lk){
          if($lk['COLUMN_KEY'] == 'PRI'){
            $is_pri .= "`".$lk['COLUMN_NAME']."`";
            $is_pri .=")";
          }

          if(empty($lk['IS_NULLABLE'])){
            $is_empty = '~';
          }else{
            $is_empty = $lk['IS_NULLABLE'] == 'YES' ? ":heavy_check_mark:" : ':heavy_minus_sign:' ;
          }

          if(is_numeric($lk['NUMERIC_SCALE'])){
            $is_number = $lk['NUMERIC_SCALE'];
          }else{
            $is_number = '~';
          }

          if(empty($lk['COLUMN_KEY'])){
            $is_key = '~';
          }else{
            $is_key = $lk['COLUMN_KEY'];
          }

          if(empty($lk['EXTRA'])){
            $is_extra = '~';
          }else{
            $is_extra = $lk['EXTRA'];
          }

          if(empty($lk['COLUMN_DEFAULT'])){
            if(is_numeric($lk['COLUMN_DEFAULT'])){
              $is_default = $lk['COLUMN_DEFAULT'];
            }else{
              $is_default = '~';
            }
          }else{
            $is_default = $lk['COLUMN_DEFAULT'];
          }

          $columnFlag = false;
          $columnStyle = $lk['COLUMN_NAME'];
          if(empty($lk['COLUMN_COMMENT'])){
            $is_comment = '~';
          }else{
            $is_comment = $lk['COLUMN_COMMENT'];
            if(str_contains($is_comment, '#discard')){
              $columnStyle = '~~'.$lk['COLUMN_NAME'].'~~';
            }
          }

          $mdContent = $mdContent . '|' . $k . '|'.$columnStyle.'|'.$lk['COLUMN_TYPE'].'|'.$is_number.'|'.$is_empty.'|'.$is_extra.'|'.$is_default.'|'.$is_key.'|'.$is_comment.'|'.PHP_EOL;
          $columnLength++;
          $k++;
          array_push($columnArray,$lk['COLUMN_NAME']);
        }
        $indexSql = "SHOW CREATE TABLE ".$row['TABLE_NAME'];
        $indexList = $this->connection->query($indexSql);
        $newIndex = $indexList->fetch($this->connection::FETCH_ASSOC);
        $ddl .= $newIndex['Create Table'];
        $ddl .= ';';
        $mdContent = $mdContent . PHP_EOL;
        $mdContent = $mdContent . '## DDL'.PHP_EOL.PHP_EOL;
        $mdContent = $mdContent . '```sql'.PHP_EOL;
        $mdContent = $mdContent . $ddl.PHP_EOL;
        $mdContent = $mdContent . '```'.PHP_EOL;
      }

      $mdContent = $mdContent . '- 本页最后更新时间: '.date('Y-m-d H:i:s').PHP_EOL.PHP_EOL;
      file_put_contents($fileName,$mdContent);
      $mdContent = '';
    }
    array_multisort(array_column($tableData,'table'),SORT_ASC,$tableData);
    foreach ($tableData as $item =>$value){
      $summaryContent = $summaryContent . '  * 【'.$value['capacity'].'】 ['.$value['table'].'('.$value['name'].')](/2-table'.DIRECTORY_SEPARATOR.$value['table'].'.md)'.PHP_EOL;
    }
    $summaryContent .= PHP_EOL;
    $summaryContent .= '```shell'.PHP_EOL;
    $summaryContent .= '最后更新时间:'.date("Y-m-d H:i:s").PHP_EOL;
    $summaryContent .= '```';
    file_put_contents($summary,$summaryContent);
    unset($tableData);
    // 增加额外的调试信息
    $beginTime = App::getInstance()->getBeginTime();

    $runtime = round(microtime(true) - $beginTime, 10);
    $beginMem = App::getInstance()->getBeginMem();
    $reqs = $runtime > 0 ? number_format(1 / $runtime, 2) : '∞';
    $memory_use = number_format((memory_get_usage() - $beginMem) / 1024, 2);
    $time_str = ' [运行时间：' . number_format($runtime, 6) . 's] [吞吐率：' . $reqs . 'req/s]';
    $memory_str = ' [内存消耗：' . $memory_use . 'kb]';
    $file_load = ' [文件加载：' . count(get_included_files()) . ']';

    echo PHP_EOL;
    echo ' 执行结束!'.PHP_EOL;
    echo PHP_EOL;
    echo $time_str.PHP_EOL;
    echo $memory_str.PHP_EOL;
    echo $file_load.PHP_EOL;
    echo ' 本次更新时间'.date('Y-m-d H:i:s').PHP_EOL;
  }
}
